package aQuery;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;

public class Packets {

	static Connection connection;
	
	public static void main(String[] args) throws Exception {
		connect();
		//drop();
		//create();
		//populate();
		display();
		disconnect();
	}

	public static void disconnect() throws Exception {
		connection.close();
	}
	
	public static void connect() throws Exception {
		String u = "jcbunix";
		String p = "je";
		String db = "jcbunix";
    	String host = "gandalf.umcs.maine.edu";
	    connection = DriverManager.getConnection("jdbc:postgresql://" + host + "/" + db, u, p);
	}
	
	
	public static void create() throws Exception {
		PreparedStatement createTablesStatement = connection.prepareStatement(
    		"CREATE SEQUENCE pID;" + 
			"CREATE TABLE Packets (" + 
				"pID INTEGER PRIMARY KEY UNIQUE DEFAULT NEXTVAL('pID')," + 
				"src VARCHAR(100)," + 
				"dest VARCHAR(100)," + 
				"length INTEGER," + 
				"ts TIMESTAMP" + 
			");"
    	);
    	createTablesStatement.execute();
	}
	
	public static void drop() throws Exception {
		PreparedStatement dropTablesStatement = connection.prepareStatement(
    		"DROP SEQUENCE pID;" + 
			"DROP TABLE Packets CASCADE;"
    	);
		dropTablesStatement.execute();
	}
	
	public static void populate() throws Exception {
    	PreparedStatement addPacket = connection.prepareStatement(
			"INSERT INTO Packets (src, dest, length, ts) VALUES (?, ?, ?, ?);"
		);
    	for(int i=0; i<10; i++) {
			addPacket.setString(1,"a");
			addPacket.setString(2,"b");
			addPacket.setInt(3, 1);
			addPacket.setTimestamp(4, new Timestamp((33+i)*1000)); //modified as needed
			addPacket.executeUpdate();
    	}
	}
	
	public static void display() throws Exception {
		int gap = 120;
		PreparedStatement query = connection.prepareStatement(
			"SELECT COUNT(p.pID) AS total " +
			"FROM Packets p, Packets h, Packets t, (" +
				"SELECT heads.pID AS headPID, MIN(tails.pID) AS tailPID " + 
				"FROM (" +
					"SELECT * " +
					"FROM Packets " + 
					"WHERE pID NOT IN (" +
			    		"SELECT p2.pID " +
			    		"FROM Packets p1, Packets p2 " + 
			    		"WHERE p1.ts < p2.ts " +
			    		"AND p1.ts + CAST(? || ' SECOND' AS INTERVAL) >= p2.ts" +
					")" +
			    ") AS heads, (" +
					"SELECT * " +
					"FROM Packets " + 
					"WHERE pID NOT IN (" +
			    		"SELECT p1.pID " +
			    		"FROM Packets p1, Packets p2 " + 
			    		"WHERE p1.ts < p2.ts " +
			    		"AND p1.ts + CAST(? || ' SECOND' AS INTERVAL) >= p2.ts" +
					")" +
			    ") AS tails " + 
				"WHERE heads.ts <= tails.ts " + 
				"GROUP BY heads.pID" +
			") AS streams " +
			"WHERE streams.headPID = h.pID " + 
			"AND streams.tailPID = t.pID " + 
			"AND h.ts <= p.ts " + 
			"AND p.ts <= t.ts " +
			"GROUP BY h.pID"
    	);
		query.setInt(1, gap);
		query.setInt(2, gap);
		query.execute();
		
    	ResultSet result = query.getResultSet();
    	while(result.next()) {
	    	List<Object> params = new LinkedList<Object>();

	    	params.add(result.getInt("total"));
//	    	params.add(result.getString("src"));
//	    	params.add(result.getString("dest"));
//	    	params.add(result.getInt("length"));
//	    	params.add(result.getTimestamp("ts"));
	    	
	    	System.out.println(params);
    	}
	}
	
}
